package com.th.dom.utils;

import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigInteger;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 

* <p>Title: ExcelUtils</p>  

* <p>Description: excel导入导出工具类</p>  

* @author dove  

* @date 2018年7月23日上午10:15:00
 */
public class ExcelUtils {
	/**
	 * 
	  * @Description: 解析xls结尾的excel
	  * @author : dove
	  * @param filePath 文件路径
	  * @return
	  * @throws Exception 
	  * @date  2018年7月23日上午10:17:45
	 */
	public static List<Map<String,Object>> readXls(String filePath) throws Exception {
		InputStream is = new FileInputStream(filePath);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);//HSSFWorkbook表示整个Excel
		List<Map<String,Object>> result = new ArrayList<>();
		//循环每一页，并处理当前的循环页
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);//HSSFSheet表示某一页
			if (hssfSheet == null) {
				continue;
			}
			//记录title
			List<String> title = new ArrayList<String>();
			HSSFRow row = hssfSheet.getRow(0);//HSSFRow表示每一行的数据
			for (int i =0; i < row.getLastCellNum(); i++) {
				HSSFCell cell = row.getCell(i);
				title.add(getStringVal(cell));
			}
			Map<String,Object> map = null;
			//处理当前页，循环处理每一行的数据
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);//HSSFRow表示每一行的数据
				int minColIx = hssfRow.getFirstCellNum();
				int maxColIx = hssfRow.getLastCellNum();
				map = new HashMap<String,Object>();
				//遍历该行，并获取每一个cell的数据
				for (int colIx = minColIx; colIx < maxColIx; colIx++) {
					HSSFCell hssfCell = hssfRow.getCell(colIx);
					if (hssfCell == null) {
						continue;
					}
					map.put(title.get(colIx), getStringVal(hssfCell));
				}
				result.add(map);
			}
			
		}
		hssfWorkbook.close();
		is.close();
		return result;
	}
	/**
	 *  
	  * @Description: 解析xls结尾的excel
	  * @author : dove
	  * @param is 输入流
	  * @return
	  * @throws Exception 
	  * @date  2018年7月23日上午10:20:56
	 */
	public static List<Map<String,String>> readXls(InputStream is) throws Exception {
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);//HSSFWorkbook表示整个Excel
		List<Map<String,String>> result = new ArrayList<>();
		//循环每一页，并处理当前的循环页
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);//HSSFSheet表示某一页
			if (hssfSheet == null) {
				continue;
			}
			//记录title
			List<String> title = new ArrayList<String>();
			HSSFRow row = hssfSheet.getRow(0);//HSSFRow表示每一行的数据
			for (int i =0; i < row.getLastCellNum(); i++) {
				HSSFCell cell = row.getCell(i);
				title.add(getStringVal(cell));
			}
			Map<String,String> map = null;
			//处理当前页，循环处理每一行的数据
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);//HSSFRow表示每一行的数据
				if (hssfRow != null) {
					int minColIx = hssfRow.getFirstCellNum();
					int maxColIx = hssfRow.getLastCellNum();
					map = new HashMap<String,String>();
					//遍历该行，并获取每一个cell的数据
					for (int colIx = minColIx; colIx < maxColIx; colIx++) {
						HSSFCell hssfCell = hssfRow.getCell(colIx);
						if (hssfCell == null) {
							continue;
						}
						map.put(title.get(colIx), getStringVal(hssfCell));
						
					}
					result.add(map);
				}
			}
			
		}
		hssfWorkbook.close();
		is.close();
		return result;
	}
	/**
	 * 
	  * @Description: 解析xlsx结尾的excel
	  * @author : dove
	  * @param filePath 文件路径
	  * @return
	  * @throws Exception 
	  * @date  2018年7月23日上午10:19:05
	 */
	public static List<Map<Object,Object>> readXlsx(String filePath) throws Exception {
        InputStream is = new FileInputStream(filePath);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<Map<Object,Object>> result = new ArrayList<>();
        //循环每一页，并处理当前的循环页
        for (Sheet sheet : xssfWorkbook) {
            if (sheet == null) {
                continue;
            }
          //记录title
			List<Object> title = new ArrayList<Object>();
			Row row2 = sheet.getRow(0);//HSSFRow表示每一行的数据
			for (int i =0; i < row2.getLastCellNum(); i++) {
				Cell cell = row2.getCell(i);
				title.add(getObjValue(cell));
			}
			Map<Object,Object> map = null;
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);//Row表示每一行的数据
                int minColIx = row.getFirstCellNum();
                int maxColIx = row.getLastCellNum();
                //遍历该行，并获取每一个cell的数据
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    Cell cell = row.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    map = new HashMap<Object,Object>();
					map.put(title.get(colIx), getObjValue(cell));
                }
                result.add(map);
            }
        }
        xssfWorkbook.close();
        is.close();
        return result;
    }
	/**
	 * 
	  * @Description: 解析xlsx结尾的excel
	  * @author : dove
	  * @param is 文件输入流
	  * @return
	  * @throws Exception 
	  * @date  2018年7月23日上午10:21:56
	 */
	
	public static List<Map<Object,Object>> readXlsx(InputStream is) throws Exception {
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<Map<Object,Object>> result = new ArrayList<>();
        //循环每一页，并处理当前的循环页
        for (Sheet sheet : xssfWorkbook) {
            if (sheet == null) {
                continue;
            }
          //记录title
			List<Object> title = new ArrayList<Object>();
			Row row2 = sheet.getRow(0);//HSSFRow表示每一行的数据
			for (int i =0; i < row2.getLastCellNum(); i++) {
				Cell cell = row2.getCell(i);
				title.add(getObjValue(cell));
			}
			Map<Object,Object> map = null;
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);//Row表示每一行的数据
                int minColIx = row.getFirstCellNum();
                int maxColIx = row.getLastCellNum();
                //遍历该行，并获取每一个cell的数据
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    Cell cell = row.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    map = new HashMap<Object,Object>();
					map.put(title.get(colIx), getObjValue(cell));
                }
                result.add(map);
            }
        }
        xssfWorkbook.close();
        is.close();
        return result;
    }
	
	@SuppressWarnings("deprecation")
	private static String getStringVal(HSSFCell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "true" : "false";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }

	
	@SuppressWarnings("deprecation")
	public static Object getObjValue(HSSFCell cell) {
		Object o = null;
		int cellType = cell.getCellType();
		switch (cellType) {
		case XSSFCell.CELL_TYPE_BLANK:
			o = "";
			break;
		case XSSFCell.CELL_TYPE_BOOLEAN:
			o = cell.getBooleanCellValue();
			break;
		case XSSFCell.CELL_TYPE_ERROR:
			o = "Bad value!";
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			o = getValueOfNumericCell(cell);
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			try {
				o = getValueOfNumericCell(cell);
			} catch (IllegalStateException e) {
				try {
					o = cell.getRichStringCellValue().toString();
				} catch (IllegalStateException e2) {
					o = cell.getErrorCellValue();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			break;
		default:
			o = cell.getRichStringCellValue().getString();
		}
		return o;
	}
	@SuppressWarnings("deprecation")
	public static Object getObjValue(Cell cell) {
        Object o = null;
        int cellType = cell.getCellType();
        switch (cellType) {
        case XSSFCell.CELL_TYPE_BLANK:
            o = "";
            break;
        case XSSFCell.CELL_TYPE_BOOLEAN:
            o = cell.getBooleanCellValue();
            break;
        case XSSFCell.CELL_TYPE_ERROR:
            o = "Bad value!";
            break;
        case XSSFCell.CELL_TYPE_NUMERIC:
            o = getValueOfNumericCell(cell);
            break;
        case XSSFCell.CELL_TYPE_FORMULA:
            try {
                o = getValueOfNumericCell(cell);
            } catch (IllegalStateException e) {
                try {
                    o = cell.getRichStringCellValue().toString();
                } catch (IllegalStateException e2) {
                    o = cell.getErrorCellValue();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            break;
        default:
            o = cell.getRichStringCellValue().getString();
        }
        return o;
    }
	
	/**
	 * 
	 * @Description: 获取数字类型的cell值
	 * @author : dove
	 * @param cell
	 * @return 
	 * @date  2018年7月23日上午11:20:18
	 */
	private static Object getValueOfNumericCell(HSSFCell cell) {
		Boolean isDate = DateUtil.isCellDateFormatted(cell);
		Double d = cell.getNumericCellValue();
		Object o = null;
		if (isDate) {
			o = DateFormat.getDateTimeInstance()
					.format(cell.getDateCellValue());
		} else {
			o = getRealStringValueOfDouble(d);
		}
		return o;
	}
	/**
	 * 
	  * @Description: 获取数字类型的cell值
	  * @author : dove
	  * @param cell
	  * @return 
	  * @date  2018年7月23日上午11:20:18
	 */
    private static Object getValueOfNumericCell(Cell cell) {
        Boolean isDate = DateUtil.isCellDateFormatted(cell);
        Double d = cell.getNumericCellValue();
        Object o = null;
        if (isDate) {
            o = DateFormat.getDateTimeInstance()
                    .format(cell.getDateCellValue());
        } else {
            o = getRealStringValueOfDouble(d);
        }
        return o;
    }
    
    /**
     * 
      * @Description: 处理科学计数法与普通计数法的字符串显示，尽最大努力保持精度
      * @author : dove
      * @param d
      * @return 
      * @date  2018年7月23日上午11:20:04
     */
    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            // 小数部分
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
                    + BigInteger.ONE.intValue(), indexOfE));
            // 指数
            int pow = Integer.valueOf(doubleStr.substring(indexOfE
                    + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            java.util.regex.Pattern p = Pattern.compile(".0$");
            java.util.regex.Matcher m = p.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }
}
